package com.uottawa.library.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.uottawa.library.domain.Loan;

public class LoanDAO extends BaseDAO {


	public Loan getLoan(int loanId) {
		String query = "Select * FROM LOAN WHERE loan_id = '"+ loanId+"'"; 
		Connection connection = getConnection();
		Loan loan = null;
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
					loan = new Loan();
					loan.setLoanId(loanId);
					loan.setItemId(rs.getInt("item_id"));
					loan.setUserId(rs.getInt("userid"));
					loan.setExpiryDate(rs.getTimestamp("expiry_date"));
					loan.setExtensions(rs.getInt("extension"));
					loan.setLoanPriodId(rs.getInt("period_id"));
					loan.setReturned(rs.getBoolean("returned"));
					loan.setLateFees(rs.getDouble("late_fees"));

				
					}
				}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return loan;
	}
	
	public Loan getLoanByItemId(int itemId) {
		
		String query = "Select * FROM LOAN WHERE ITEM_ID = '"+ itemId+"'"; 
		Connection connection = getConnection();
		Loan loan = null;
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
					loan = new Loan();
					loan.setLoanId(rs.getInt("loan_id"));
					loan.setItemId(itemId);
					loan.setUserId(rs.getInt("userid"));
					loan.setExpiryDate(rs.getTimestamp("expiry_date"));
					loan.setExtensions(rs.getInt("extension"));
					loan.setLoanPriodId(rs.getInt("period_id"));
					loan.setReturned(rs.getBoolean("returned"));
					loan.setLateFees(rs.getDouble("late_fees"));

					}
			
				}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return loan;
	}

	public boolean addLoan(Loan loan) {
		String query = "INSERT INTO LOAN (USERID, ITEM_ID, EXPIRY_DATE) " +
				"VALUES (?, ?, ?)"; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			stmt.setInt(1, loan.getUserId());
			stmt.setInt(2, loan.getItemId());
			stmt.setTimestamp(3, loan.getExpiryDate());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	
	public boolean returnLoan(Loan loan) {
		String query = "UPDATE LOAN SET RETURNED = 1 WHERE LOAN_ID = '" + loan.getLoanId() + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}

	public int NumberOfExtenstions(Loan loan) {
		int numberOfExtenstions = -1;
		String query = "Select extension FROM LOAN WHERE loan_id = '"+ loan.getLoanId()+"'"; 
		Connection connection = getConnection();

		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
					
					numberOfExtenstions = rs.getInt("extension");
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return numberOfExtenstions;		
	}
	
	public int maxLoanId() {
		int max = -1;
		String query = "Select max(loan_id) as max FROM LOAN"; 
		Connection connection = getConnection();

		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
					
					max = rs.getInt("max");
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		
		return max;		
	}

	public boolean updateLoan(Loan loan) {
		String query = "UPDATE `team7library`.`loan` SET `extension`='"+loan.getExtensions()+"',expiry_date='"+loan.getExpiryDate()+"',period_id ='"+loan.getLoanPriodId()+"' WHERE `loan_id`="+loan.getLoanId(); 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		}
		return true;
		
	}
	public List<Loan> getLoansWithFeesForUser(int userId) {
		List<Loan> loans = new ArrayList<Loan>();
		String query = "SELECT * FROM LOAN WHERE LATE_FEES > 0 AND USERID = '"+ userId+"' ORDER BY RETURNED DESC"; 
		Connection connection = getConnection();

		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				while(rs.next())
				{
					Loan loan = new Loan();
					loan.setLoanId(rs.getInt("loan_id"));
					loan.setItemId(rs.getInt("item_id"));
					loan.setUserId(rs.getInt("userid"));
					loan.setLateFees(rs.getDouble("late_fees"));
					loan.setReturned(rs.getBoolean("RETURNED"));
					loan.setLateFees(rs.getDouble("late_fees"));
					loans.add(loan);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return loans;
	}
	
	public boolean chargeFees(int userId, int itemId, double fees ) {
		String query = "UPDATE LOAN SET LATE_FEES = ? WHERE USERID = ? AND ITEM_ID = ?"; 
		Connection connection = getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(query);
			stmt.setDouble(1, fees);
			stmt.setInt(2, userId);
			stmt.setInt(3, itemId);
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}
	
	public boolean payFees(int loanId) {
		String query = "UPDATE LOAN SET LATE_FEES = 0 WHERE LOAN_ID = '" + loanId + "'"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}
	public List<Loan> getAllUserExtendableLoan(int userId,int maxRenewals) {
		// Loans that can be extended 
		
		// All user's loans 
		//isReturn = 0;
		// exp date is before know 
		// less than max number of renewals.
		List<Loan> loans =null;
		String query = "Select * FROM LOAN WHERE EXPIRY_DATE > NOW() AND RETURNED = '0' AND EXTENSION <'"+maxRenewals +"' AND USERID='"+userId+"'"; 
		Connection connection = getConnection();
		Loan loan = null;
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				loans = new ArrayList<Loan>();
				while(rs.next())
				{
					loan = new Loan();
					loan.setLoanId(rs.getInt("loan_id"));
					loan.setItemId(rs.getInt("item_id"));
					loan.setUserId(rs.getInt("userid"));
					loan.setExpiryDate(rs.getTimestamp("expiry_date"));
					loan.setExtensions(rs.getInt("extension"));
					loan.setLoanPriodId(rs.getInt("period_id"));
					loan.setReturned(rs.getBoolean("returned"));
					loan.setLateFees(rs.getDouble("late_fees"));

					loans.add(loan);
					}
			
				}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return loans;
	}
	public List<Loan> getAllLoan() {
		List<Loan> loans =null;
		String query = "Select * FROM LOAN";
		Connection connection = getConnection();
		Loan loan = null;
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				loans = new ArrayList<Loan>();
				while(rs.next())
				{
					loan = new Loan();
					loan.setLoanId(rs.getInt("loan_id"));
					loan.setItemId(rs.getInt("item_id"));
					loan.setUserId(rs.getInt("userid"));
					loan.setExpiryDate(rs.getTimestamp("expiry_date"));
					loan.setExtensions(rs.getInt("extension"));
					loan.setLoanPriodId(rs.getInt("period_id"));
					loan.setReturned(rs.getBoolean("returned"));
					loan.setLateFees(rs.getDouble("late_fees"));

					loans.add(loan);
					}
			
				}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return loans;
	}
	public List<Loan> getAllUserLLoan(int userId) {
		List<Loan> loans =null;
		String query = "Select * FROM LOAN WHERE RETURNED = '0' and USERID ='"+userId+"'";
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			loans = new ArrayList<Loan>();
			if(rs !=null)
			{
				
while(rs.next())
				{
					Loan loan = new Loan();
					loan.setLoanId(rs.getInt("loan_id"));
					loan.setItemId(rs.getInt("item_id"));
					loan.setUserId(rs.getInt("userid"));
					loan.setExpiryDate(rs.getTimestamp("expiry_date"));
					loan.setExtensions(rs.getInt("extension"));
					loan.setLoanPriodId(rs.getInt("period_id"));
					loan.setReturned(rs.getBoolean("returned"));
					loan.setLateFees(rs.getDouble("late_fees"));
					loans.add(loan);
					}
			
				}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return loans;
	}
	public List<Loan> getActiveLoansByTitleIsbn(int id) {
		List<Loan> list = new ArrayList<Loan>();
		String query = "Select * FROM LOAN WHERE RETURNED = 0 AND ITEM_ID " +
				"IN (SELECT ITEM_ID FROM ITEM WHERE ISBN = "+  id +")"; 
		Connection connection = getConnection();
		Loan loan = null;
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				while(rs.next())
				{
					loan = new Loan();
					loan.setLoanId(rs.getInt("loan_id"));
					loan.setItemId(rs.getInt("item_id"));
					loan.setUserId(rs.getInt("userid"));
					loan.setExpiryDate(rs.getTimestamp("expiry_date"));
					loan.setExtensions(rs.getInt("extension"));
					loan.setLoanPriodId(rs.getInt("period_id"));
					loan.setReturned(rs.getBoolean("returned"));
					loan.setLateFees(rs.getDouble("late_fees"));
					list.add(loan);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
		
	}




}
